/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import entities.VacancyDisplay;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 *
 * @author MrChinh
 */
public class DisplayWorkDao {

    ConnectDB myConn = new ConnectDB();
    Connection conn = null;

    public VacancyDisplay getVacancyDisplayById(String vacancyId, String workPlaceId) {
        VacancyDisplay v = new VacancyDisplay();
        try {
            conn = myConn.getConnect();
            StringBuilder sqlBuider = new StringBuilder();
            sqlBuider.append("SELECT ");
            sqlBuider.append("V.VacancyID, ");
            sqlBuider.append("V.Title, ");
            sqlBuider.append("V.Position, ");
            sqlBuider.append("C.CategoryName, ");
            sqlBuider.append("V.WorkType, ");
            sqlBuider.append("VP.workPlateName, ");
            sqlBuider.append("V.Salary, ");
            sqlBuider.append("V.Description, ");
            sqlBuider.append("V.NumberRequirement, ");
            sqlBuider.append("V.Entitlement, ");
            sqlBuider.append("V.YearOfExperience, ");
            sqlBuider.append("V.Degree, ");
            sqlBuider.append("V.Gender, ");
            sqlBuider.append("V.RequirementAge, ");
            sqlBuider.append("V.SkillRequirement, ");
            sqlBuider.append("V.profile,V.Deadline, ");
            sqlBuider.append("V.Submissionforms ");
            sqlBuider.append("FROM ");
            sqlBuider.append("Vacancy V ");
            sqlBuider.append("inner join Category C on C.CategoryID = V.CategoryID ");
            sqlBuider.append("inner join VacancyWorkPlace VVP on V.VacancyID = VVP.VacancyID ");
            sqlBuider.append("inner join WorkingPlace VP on VP.workPlaceId = VVP.workPlaceId ");
            sqlBuider.append("WHERE V.VacancyID = '");
            sqlBuider.append(vacancyId);
            sqlBuider.append("' AND VP.workPlaceId ='");
            sqlBuider.append(workPlaceId);
            sqlBuider.append("'");
            String sql = sqlBuider.toString();

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                v.setId(rs.getString("VacancyID"));
                v.setTitle(rs.getString("Title"));
                v.setPosition(rs.getString("Position"));
                v.setCategoryName(rs.getString("CategoryName"));
                v.setWorkType(rs.getString("WorkType"));
                v.setWorkPlace(rs.getString("workPlateName"));
                v.setSalary(rs.getString("Salary"));
                v.setDescription(rs.getString("Description"));
                v.setNumberRequirement(rs.getString("NumberRequirement"));
                v.setEntitlement(rs.getString("Entitlement"));
                v.setYearOfExperience(rs.getString("YearOfExperience"));
                v.setDegree(rs.getString("Degree"));
                v.setRequirementGender(rs.getString("Gender"));
                v.setRequirementAge(rs.getString("RequirementAge"));
                v.setRequirementOther(rs.getString("SkillRequirement"));
                v.setProfileIncluding(rs.getString("profile"));
                Date dateC = rs.getDate("Deadline");
                SimpleDateFormat fd = new SimpleDateFormat("dd/MM/yy");
                v.setDeadlineForSubmission(fd.format(dateC));
                v.setSubmissionType(rs.getString("Submissionforms"));
            }

        } catch (Exception e) {
        }
        return v;
    }
}
